
package com.kevin.demo.utils;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;


/**
 * Author:   baili
 * Date:     2018/5/15 21:12
 * Description:
 * 指定实体对象全限名 导入excel 返回实体对象List
 * 注意：1. 实体类所有的属性都小写 2.excel的sheet名和文件名一致 3.excel日期数据为日期格式或 yyyy-MM-dd HH:mm:ss 字符
 * 标题两行：第一行中文标题，第二行英文标题，读取数据从第三行开始读取
 *
 * 导入模板：第二行中文标题，第二行英文标题
 */

public class ExcelImportUtil<T> {

    private String excel2003L = ".xls";    //2003- 版本的excel
    private String excel2007U = ".xlsx";   //2007+ 版本的excel

    private final Logger log = LoggerFactory.getLogger(this.getClass());


    //public static void main(String[] args) throws Exception {
    //
    //    File file = new File("C:\\Users\\e20160504\\Desktop\\standardprice.xls");
    //
    //    FileInputStream fileStream=new FileInputStream(file);
    //
    //    ExcelImportUtil<SkuChangeStandardPricedt> s = new ExcelImportUtil();
    //    List<SkuChangeStandardPricedt> list = s.saveExcel("com.jzez.product.entity.sku.changeprice.SkuChangeStandardPricedt",fileStream, "C:\\Users\\e20160504\\Desktop\\standardprice.xls");
    //    for (SkuChangeStandardPricedt c1 : list) {
    //        System.out.println(c1.toString());
    //    }
    //}

    /**
     * 读取excel文件内容到list中
     * obj:String 实体类的全路径名 package.classname
     * 返回一个对象的list
     */

    //读取文件
    public List<T> saveExcel(MultipartFile file, String obj) throws Exception {

        InputStream in = file.getInputStream();
        String fileName = file.getOriginalFilename();
        //System.out.println(fileName);
        return saveExcel(obj, in, fileName);
    }


    //读取流
    public List<T> saveExcel(String obj, InputStream in, String fileName) throws Exception {

        System.out.println("文件名 " + fileName);

        Map<Integer, String> titlMap = new HashMap<Integer, String>();

        //文件名
        File tempFile = new File(fileName.trim());
        String shortFileName = tempFile.getName();
        shortFileName = shortFileName.substring(0, shortFileName.lastIndexOf("."));

        List<T> list = new ArrayList<>();
        //获取excel文件
        Workbook wb;
        try {
            wb = getWorkbook(in, fileName);
            //获得sheet总数量
            int sheetNums = 1; //wb.getNumberOfSheets();
            //总行数
            int rowTotalNum = 0;
            //总列数
            int celTotalNum = 0;
            //一个行对象
            Row xssfRow = null;
            //一个列对象
            Cell xssfcell = null;
            //循环遍历所有sheet
            for (int numSheet = 0; numSheet < sheetNums; numSheet++) {
                //查找sheet名为CLC的sheet
                String sheetNam = wb.getSheetName(numSheet);

                //if (!shortFileName.trim().equalsIgnoreCase(sheetNam)) {
                //    continue;
                //}
                Sheet xssfSheet = wb.getSheetAt(numSheet);
                //获得总行数
                rowTotalNum = xssfSheet.getLastRowNum();
                // 读取每行数据封装到对象中
                for (int rowNum = 1; rowNum <= rowTotalNum; rowNum++) {
                    if (rowNum == 1) {//如果是第一行，读取每一个标题并记录序号，存储到map中
                        xssfRow = xssfSheet.getRow(rowNum);//行对象
                        celTotalNum = xssfRow.getLastCellNum();//总列数
                        // 循环遍历每一行的所有列
                        for (int c = 0; c < celTotalNum; c++) {
                            xssfcell = xssfRow.getCell((short) c);
                            //将列名转为对象中的属性名
                            String strCel = xssfcell.toString().replaceAll("\\s*", "").replaceAll("_", "");
                            titlMap.put(c, strCel);

                        }
                    }
                    //读取非第一行的正文数据，将每一行的数据存储到对应属性的对象中
                    else {
                        xssfRow = xssfSheet.getRow(rowNum);//行对象
                        if(xssfRow == null){
                            throw new Exception("请检查数据，第" + rowNum + " 行是空行");
                        }
                        //一行使用一个对象
                        T customerListMain = null;
                        try {
                            try {
                                customerListMain = (T) Class.forName(obj).newInstance();
                            } catch (InstantiationException e) {
                                e.printStackTrace();
                            }
                        } catch (ClassNotFoundException e) {
                            e.printStackTrace();
                        }

                        //遍历该行的所有列
                        for (int c = 0; c < celTotalNum; c++) {

                            xssfcell = xssfRow.getCell((short) c);

                            //if(HSSFDateUtil.isCellDateFormatted(xssfcell)){

                            String titlName = titlMap.get(c);
                            //除id以外，其余属性均为String类型
                            if (!"id".equals(titlName)) {
                                //利用返射，根据属性名，得到set方法，并为set赋值
                                Field field = customerListMain.getClass().getDeclaredField(titlName);//根据属性名获取属性值
                                String type = customerListMain.getClass().getDeclaredField(titlName).getGenericType().toString();
                                field.setAccessible(true);//属性为private属性设置为true
                                //System.out.println("column " + titlName);
                                try {
                                    field.set(customerListMain, getCellValue(type, xssfcell));//为set方法赋值
                                } catch (Exception e) {
                                    throw new Exception("第" + (rowNum + 1) + "行，第" + (c + 1) + "列 数据有问题" + e.getMessage());
                                }
                            }
                        }
                        list.add(customerListMain);
                    }
                }
            }

        } catch (IOException e) {
        }

        return list;
    }


    /**
     * 描述：根据文件后缀，自适应上传文件的版本
     */
    public Workbook getWorkbook(InputStream in, String fileName) throws Exception {
        Workbook wb = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (excel2003L.equals(fileType)) {
            wb = new HSSFWorkbook(in);  //2003-
        } else if (excel2007U.equals(fileType)) {
            wb = new XSSFWorkbook(in);  //2007+
        } else {
            throw new Exception("解析的文件格式有误！");
        }
        return wb;
    }


    //将单元格的数据装成实体类属性类型
    public Object getCellValue(String type, Cell cell) throws Exception {

        if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
            return null;
        }
        //System.out.println("====type :" + type + " " +cell.getCellType());

        if (type.equals("class java.lang.Integer")) {
            Double value;
            if(cell.getCellType() == 1){
                value = Double.valueOf(cell.getStringCellValue());
            }else {
                value = cell.getNumericCellValue();
            }
            long longVal = Math.round(value);
            if (Double.parseDouble(longVal + ".0") == value) {
                return (int) longVal;
            } else {
                throw new Exception("请用整数 单位分");
            }
        }
        if (type.equals("class java.lang.String")) {
            //如果单元格是日期格式
            if (0 == cell.getCellType()) {
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    Date d = cell.getDateCellValue();
                    DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    return formater.format(d);
                }
            }
            return cell.getStringCellValue();
        }

        if (type.equals("class java.lang.Double")) {
            if (cell.getCellType() == 1) {
                return Double.valueOf(cell.getStringCellValue());
            }
            return cell.getNumericCellValue();
        }
        if (type.equals("class java.lang.Boolean")) {
            return cell.getBooleanCellValue();
        }
        if (type.equals("class java.util.Date")) {
            //如果实体类型属性是date类型而excel类型是字符串做转换
            //System.out.println(cell.getCellType() + "======");
            if (cell.getCellType() == 1) {

                DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                try {
                    //System.out.println(cell.getStringCellValue());
                    return format.parse(cell.getStringCellValue());
                } catch (ParseException e) {
                    e.printStackTrace();
                    throw new Exception("日期转换错误日期要日期类型或格式要 yyyy-MM-dd HH:mm:ss字符串");
                }
            }
            return cell.getDateCellValue();
        }
        return null;
    }


    /**
     * 因excel的标题与位置会变，所以在此需要做一个固定标识
     *
     * @param xssfcell
     */
    private void setTile(String xssfcell, int index, Object model) {
        if (xssfcell != null && !"".equals(xssfcell)) {
            Field[] field = model.getClass().getDeclaredFields(); //获取实体类的所有属性，返回Field数组
            for (int j = 0; j < field.length; j++) { //遍历所有属性
                String name = field[j].getName(); //获取属性的名字
                //不对id进行比较
                if (j == 0) {
                    continue;
                }
                //比较对象属性与excel列名。如果去除空格，忽略大小写，一样的情况，将该列的序号
                if (xssfcell.equalsIgnoreCase(name)) {//列名与属性一致

                    System.out.println("进入 if");
                } else {
                    System.out.println("未进 if");
                }
            }

        }
    }
}
